#!/bin/perl



#
#
#db2 "update regattas set REGRESS_ORDER = REGRESS_ORDER + 1 where REGRESS_ORDER >= 1"
#db2 insert into REGATTAS values ('Milton', 1)


# Transform the csv files to map team names.  Needs to be done one time only
my $mapTeamNames = 1;
if ($mapTeamNames)
{
   system("nameTransform.pl regressionOHDBC3K2010.csv");
   system("nameTransform.pl regressionWelland2010.csv");
   system("nameTransform.pl regressionHamilton2010.csv");
   system("nameTransform.pl regressionMilton2010.csv");
   system("nameTransform.pl regressionPickering2010.csv");
   system("nameTransform.pl regressionPickeringHS2010.csv");
   system("nameTransform.pl regressionCenterIsland2010.csv");
   system("nameTransform.pl regressionGWNSport2010.csv");
   system("nameTransform.pl regressionOttawa2010.csv");
   system("nameTransform.pl regressionMontreal2010.csv");
   system("nameTransform.pl regressionWoodstock2010.csv");
   system("nameTransform.pl regressionNDCCup2010.csv");
   system("nameTransform.pl regressionParrySound2010.csv");
   system("nameTransform.pl regressionGWNC2010.csv");
   system("nameTransform.pl regressionOakville2010.csv");
   system("nameTransform.pl regressionSudbury2010.csv");
   system("nameTransform.pl regressionRawdon2010.csv");
   system("nameTransform.pl regressionLachine2010.csv");
   system("nameTransform.pl regressionStratford2010.csv");
   system("nameTransform.pl regressionQC2010.csv");
}

# Careful with opening CSV files in excel.  It will change the format of my times to mm:ss.0 so I need
# to change it back to mm:ss.00 before saving or else I will lose the 1/100 of a seconds column
system("processCSV.pl regressionOHDBC3K2010.csv");
system("processCSV.pl regressionWelland2010.csv");
system("processCSV.pl regressionHamilton2010.csv");
system("processCSV.pl regressionMilton2010.csv");
system("processCSV.pl regressionPickering2010.csv");
system("processCSV.pl regressionPickeringHS2010.csv");
system("processCSV.pl regressionCenterIsland2010.csv");
system("processCSV.pl regressionGWNSport2010.csv");
system("processCSV.pl regressionOttawa2010.csv");
system("processCSV.pl regressionMontreal2010.csv");
system("processCSV.pl regressionWoodstock2010.csv");
system("processCSV.pl regressionNDCCup2010.csv");
system("processCSV.pl regressionParrySound2010.csv");
system("processCSV.pl regressionGWNC2010.csv");
system("processCSV.pl regressionOakville2010.csv");
system("processCSV.pl regressionSudbury2010.csv");
system("processCSV.pl regressionRawdon2010.csv");
system("processCSV.pl regressionLachine2010.csv");
system("processCSV.pl regressionStratford2010.csv");
system("processCSV.pl regressionQC2010.csv");



system("db2 connect to dboat");
system("db2 drop table race");
system("db2 create table race (TEAM VARCHAR(100), CATEGORY VARCHAR(100), TIME DECIMAL(31,20), RACENO INTEGER, DISTANCE INTEGER, REGATTA VARCHAR(25), YEAR INTEGER, LANE INTEGER, RACENAME VARCHAR(50) WITH DEFAULT) ");

system("db2 load from regressionOHDBC3K2010.csv.out of del insert into race");
system("db2 load from regressionWelland2010.csv.out of del insert into race");
system("db2 load from regressionHamilton2010.csv.out of del insert into race");
system("db2 load from regressionMilton2010.csv.out of del insert into race");
system("db2 load from regressionPickering2010.csv.out of del insert into race");
system("db2 load from regressionCenterIsland2010.csv.out of del insert into race");
system("db2 load from regressionGWNSport2010.csv.out of del insert into race");
system("db2 load from regressionOttawa2010.csv.out of del insert into race");
system("db2 load from regressionMontreal2010.csv.out of del insert into race");
system("db2 load from regressionPickeringHS2010.csv.out of del insert into race");
system("db2 load from regressionWoodstock2010.csv.out of del insert into race");
system("db2 load from regressionNDCCup2010.csv.out of del insert into race");
system("db2 load from regressionParrySound2010.csv.out of del insert into race");
system("db2 load from regressionGWNC2010.csv.out of del insert into race");
system("db2 load from regressionOakville2010.csv.out of del insert into race");
system("db2 load from regressionSudbury2010.csv.out of del insert into race");
system("db2 load from regressionRawdon2010.csv.out of del insert into race");
system("db2 load from regressionLachine2010.csv.out of del insert into race");
system("db2 load from regressionStratford2010.csv.out of del insert into race");
system("db2 load from regressionQC2010.csv.out of del insert into race");



#May ?  OHDBC3K
#May 23 Milton
#June 6 Pickering
#June 7 Pickering HS
#June 13 Welland, Parry Sound, Lachine
#June 21 TIDBRF,  Ottawa
#July 4 Hamilton, Belleville sprints
#July 18 GWN Sport, Waterloo
#July 25, 26 Montreal
#Aug 8 Woodstock, Huntsville
#Aug 15 NDC
#Aug 16 Oakville
#Aug 22 Nationals
#Sep 19 Stratford
#Sep 19 QC
system("db2 drop table regattas");
system("db2 create table regattas (NAME VARCHAR(25), REGRESS_ORDER INTEGER, PRINT_ORDER INTEGER, WHEN DATE, WEBSITE VARCHAR(100), COMPETITIVE DECIMAL(4,2))");

# ---------------------- 500m regression order ----------------------
# -1 for regress_order means it will not be included in the regression
#system("db2 insert into regattas values " .
#            " ('OHDBC3K',     -1, -1,  DATE('2010-05-29'), 'http://www.ohdbc.ca/', 0), " . 
#            " ('Welland',     -1, -1,  DATE('2010-06-12'), 'http://www.dragon-boats.com/welland/', 0), " .
#            " ('Hamilton',    -1, -1,  DATE('2010-07-10'), 'http://www.dragon-boats.com/Hamilton/', 0), " .
#            " ('CenterIsland', 0,  3,  DATE('2010-06-19'), 'http://www.dragonboats.com/', 0), " .
#            " ('Milton',       1,  0,  DATE('2010-05-29'), 'http://www.dragon-boats.com/milton/', 0),  " .
#            " ('Pickering',    2,  1,  DATE('2010-06-07'), 'http://www.pdbc.ca/', 0),  " .
#            " ('PickeringHS',  3,  2,  DATE('2010-06-08'), 'http://www.pdbc.ca/', 0),  " . 
#            " ('GWNSport',     4,  5,  DATE('2010-07-17'), 'http://www.gwndragonboat.com/', 0), " .
#            " ('Montreal',     6,  6,  DATE('2010-07-24'), 'http://www.montrealdragonboat.com/', 0),  " .
#            " ('Ottawa',       5,  4,  DATE('2010-06-19'), 'http://www.dragonboat.net/', 0) "
#      ); 


system("db2 insert into regattas values " .
            " ('OHDBC3K',     -1, -1,  DATE('2010-05-29'), 'http://www.ohdbc.ca/', 0), " . 
            " ('Welland',      7,  3,  DATE('2010-06-12'), 'http://www.dragon-boats.com/welland/', 0), " .
            " ('Hamilton',     8,  6,  DATE('2010-07-10'), 'http://www.dragon-boats.com/Hamilton/', 0), " .
            " ('CenterIsland', 0,  4,  DATE('2010-06-19'), 'http://www.dragonboats.com/', 0), " .
            " ('Milton',       1,  0,  DATE('2010-05-29'), 'http://www.dragon-boats.com/milton/', 0),  " .
            " ('Pickering',    2,  1,  DATE('2010-06-07'), 'http://www.pdbc.ca/', 0),  " . 
            " ('PickeringHS',  3,  2,  DATE('2010-06-08'), 'http://www.pdbc.ca/', 0),  " . 
            " ('GWNSport',     4,  7,  DATE('2010-07-17'), 'http://www.gwndragonboat.com/', 0), " .
            " ('Sudbury',     -1, -1,  DATE('2010-07-16'), 'http://www.sudburydragonboats.org/', 0), " .
            " ('Montreal',     6,  8,  DATE('2010-07-24'), 'http://www.montrealdragonboat.com/', 0),  " .
            " ('Ottawa',       5,  5,  DATE('2010-06-19'), 'http://www.dragonboat.net/', 0), " .
            " ('Woodstock',       9,  9,  DATE('2010-08-07'), 'http://www.dragonboatwoodstock.ca', 0),  " . 
            " ('NDC Toronto Cup',-1, -1,  DATE('2010-08-14'), 'http://www.nationaldragonboat.com/', 0), " .
            " ('Oakville',        11, 11, DATE('2010-08-15'), 'http://www.oakvilledragon.com/', 0), " .            
            " ('ParrySound',     -1, -1,  DATE('2010-06-12'), 'http://www.dragonboatfestival.org/', 0),  " .
            " ('Rawdon',         10, 10,  DATE('2010-08-13'), 'http://www.22dragons.com/site/?q=en/node/48', 0),  " .
            " ('Lachine',        -1, -1,  DATE('2010-06-10'), 'http://www.22dragons.com/site/?q=en/node/49', 0),  " .
            " ('GWNC',            12, 12, DATE('2010-09-12'), 'http://www.gwndragonboat.com/', 0), " . 
            " ('QC',              13, 13, DATE('2010-09-18'), 'http://www.22dragons.com/', 0), " .
            " ('Stratford',       14, 14, DATE('2010-09-18'), 'http://www.rotarystratford.com/dragonboat/index.htm', 0) "
            
      ); 



# ---------------------- 200m regression order ----------------------
#system("db2 insert into regattas values ('Pickering', 0, 0, 1.0), ('GWN Sport', 1, 2, 1.0), ('Montreal', 2, 3, 1.0), ('Ottawa', 3, 1, 1.0), ('Lachine', 4, 4, 1.0), ('Belleville', 5, 5, 1.0)"); 


# ---------------------- 2000m regression order ----------------------
#system("db2 insert into regattas values ('Pickering', 0, 0, 1.0), ('GWN Sport', 1, 1, 1.0), ('Woodstock', 2, 2, 1.0), ('Milton', 3, 3, 1.0), ('Hamilton', 4, 4, 1.0),  ('Belleville', 5, 5, 1.0)");


system("db2 export to race2010.del of del select * from race");
system("db2 export to dates2010.del of del select distinct(race.regatta), regattas.regress_order, regattas.print_order, regattas.when, regattas.website, regattas.competitive from race, regattas where race.regatta=regattas.name");



system("db2 connect");
system("db2 update race set team='MOFOS' where team='FURIOUS STROKERS: WRATH OF BRAWN'");
system("db2 update race set TIME=128.07 where team='CSDC' and REGATTA='Pickering' and RACENO=14");
system("db2 update race set team='NETWORK 54' where team='HYDROPHALLIC DRAGONS'");
system("db2 update race set team='NETWORK 54' where team='NEWER COLLEGE NEWER DRAGONS'");
system("db2 update race set team='NETWORK 54' where team='TAF - MON PETIT CHOU'");
system("db2 update race set team='HYDROPHOBIC DRAGONS' where team='WILL PADDLE FOR RICE'");

#system("db2 delete from race where team like 'LANE 6 7 8 9 AND 10'");
#system("db2 delete from race where team like 'SRP' and regatta like 'NDC' and raceNo=33");


print("Check for same team names with more than one gender classification: \n");
system("db2 select distinct team from race where category='Mixed' INTERSECT SELECT distinct team FROM race WHERE not category='Mixed'");

print("Check for questionable 500m times: \n");
system("db2 \"select * from race where distance=500 and (time<90 or time>240)\"");

print("Check for unknown categories: \n");
system("db2 \"select * from race where category not in ('WOMEN', 'Women', 'OPEN', 'Open', 'MIXED', 'Mixed')\" ");



system("db2 connect");
system("db2 connect reset");

